package com.example.express.dao.impl;

import com.example.express.bean.User;
import com.example.express.dao.BaseUserDao;
import com.example.express.util.DruidUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDaoImpl implements BaseUserDao {
    private static final String SQL_CONSOLE = "SELECT " +
            "COUNT(id) data4_size," +
            "COUNT(TO_DAYS(registTime)=TO_DAYS(NOW()) OR NULL) data4_day " +
            "FROM t_user";
    private static final String SQL_FIND_ALL = "SELECT * FROM t_user";

    private static final String SQL_FIND_ALL_BY_LIMIT = "SELECT * FROM t_user LIMIT ?,?";

    private static final String SQL_FIND_BY_NUMBER = "SELECT * FROM t_user WHERE phoneNumber= ?";

    private static final String SQL_INSERT = "INSERT INTO t_user(nickname,phoneNumber,idNumber,password,registTime) VALUES(?,?,?,?,NOW())";

    private static final String SQL_UPDATE = "UPDATE t_user SET nickname=?,phoneNumber=?,idNumber=?,password=? WHERE id=?";

    private static final String SQL_DELETE = "DELETE FROM t_user WHERE id=?";
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String,Integer>> data = new ArrayList<>();
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        Map<String,Integer> map = new HashMap<>();
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            rs = state.executeQuery();
            while (rs.next()){
                int data4_size = rs.getInt("data4_size");
                int data4_day = rs.getInt("data4_day");
                map.put("data4_size",data4_size);
                map.put("data4_day",data4_day);
                data.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return data;
    }

    @Override
    public List<User> findAll(boolean limit, int offset, int pageNum) {
        List<User> data = new ArrayList<>();
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            if (limit){
                state = conn.prepareStatement(SQL_FIND_ALL_BY_LIMIT);
                state.setInt(1,offset);
                state.setInt(2,pageNum);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            rs = state.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String nickname = rs.getString("nickname");
                String phoneNumber = rs.getString("phoneNumber");
                String idNumber = rs.getString("idNumber");
                String password = rs.getString("password");
                Timestamp registTime = rs.getTimestamp("registTime");
                Timestamp loginTime = rs.getTimestamp("loginTime");
                User user = new User(id,nickname,idNumber,phoneNumber,password,registTime,loginTime);
                data.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return data;
    }

    @Override
    public User findByPhoneNum(String phoneNumber) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        User user = new User();
        try {
            state = conn.prepareStatement(SQL_FIND_BY_NUMBER);
            state.setString(1,phoneNumber);
            rs = state.executeQuery();
            while(rs.next()){
                int id = rs.getInt("id");
                String nickname = rs.getString("nickname");
                String idNumber = rs.getString("idNumber");
                String password = rs.getString("password");
                user = new User(id,nickname,idNumber,phoneNumber,password);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return user;
    }

    @Override
    public boolean insert(User user) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            state.setString(1,user.getNickname());
            state.setString(2,user.getPhoneNumber());
            state.setString(3,user.getIdNumber());
            state.setString(4, user.getPassword());
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally{
            DruidUtils.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean update(int id, User user) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1,user.getNickname());
            state.setString(2,user.getPhoneNumber());
            state.setString(3,user.getIdNumber());
            state.setString(4, user.getPassword());
            state.setInt(5,user.getId());
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state =  conn.prepareStatement(SQL_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }
}
